CREATE VIEW [dbo].[vOpportunityReport]
AS

SELECT OM.Id AS OpportunityId, OM.Description AS OpportunityName, OSR.OpportunityStatusName AS OpportunityStatus, OT.Name AS OpportunityType,
       OT.OpportunityClass, OM.CurrentActionPlanStageName AS CurrentStage, OM.WinProbability, OM.ExpirationDate, OM.UpdatedOn, OM.CreatedOn,
       CASE WHEN OMS.DecisionDate IS NOT NULL THEN OMS.DecisionDate WHEN OMD.DecisionDate IS NOT NULL
       THEN OMD.DecisionDate WHEN OMM.DecisionDate IS NOT NULL THEN OMM.DecisionDate END AS DecisionDate,
       CASE WHEN OMS.ResponseMedia IS NOT NULL THEN OMS.ResponseMedia WHEN OMD.ResponseMedia IS NOT NULL
       THEN OMD.ResponseMedia WHEN OMM.ResponseMedia IS NOT NULL THEN OMM.ResponseMedia END AS ResponseMedia,
       CASE WHEN OMS.TimingProbability IS NOT NULL THEN OMS.TimingProbability WHEN OMD.TimingProbability IS NOT NULL
       THEN OMD.TimingProbability WHEN OMM.TimingProbability IS NOT NULL THEN OMM.TimingProbability END AS TimingProbability,
       CASE WHEN OMS.ActualSales IS NOT NULL THEN OMS.ActualSales WHEN OMD.Actual IS NOT NULL
       THEN OMD.Actual WHEN OMM.Actual IS NOT NULL THEN OMM.Actual END AS Actual, CASE WHEN OMS.PotentialSales IS NOT NULL
       THEN OMS.PotentialSales WHEN OMD.Potential IS NOT NULL THEN OMD.Potential WHEN OMM.Potential IS NOT NULL
       THEN OMM.Potential END AS Potential, CASE WHEN OMS.Quality IS NOT NULL THEN OMS.Quality WHEN OMD.Quality IS NOT NULL
       THEN OMD.Quality WHEN OMM.Quality IS NOT NULL THEN OMM.Quality END AS Quality, CASE WHEN OMS. Product IS NOT NULL
       THEN OMS. Product WHEN OMD.Distribution IS NOT NULL THEN OMD.Distribution WHEN OMM.Subscription IS NOT NULL
       THEN OMM.Subscription END AS ProductInterest, CASE WHEN OMS.Units IS NOT NULL THEN OMS.Units WHEN OMM.Units IS NOT NULL
       THEN OMM.Units ELSE 0 END AS Units, P.ID AS ProspectId, P.FullName AS ProspectName, I.InstituteName AS ProspectCompany,
       G.Name AS GroupName, G.GroupTypeKey, CSM.ID AS OwnerId, CSM.FullName AS OwnerName, GRR.GroupRoleName AS OwnerRole,
       OM.OpportunityKey, OT.OpportunityTypeKey, OM.OpportunityOwnerGroupKey, OM.OpportunityContactGroupKey, OM.SourceCodeKey
FROM dbo.OpportunityMain AS OM INNER JOIN
     dbo.OpportunityType AS OT ON OM.OpportunityTypeKey = OT.OpportunityTypeKey LEFT OUTER JOIN
     dbo.OpportunityStatusRef AS OSR ON OM.OpportunityStatusCode = OSR.OpportunityStatusCode LEFT OUTER JOIN
     dbo.OpportunityMainSales AS OMS ON OM.OpportunityKey = OMS.OpportunityKey LEFT OUTER JOIN
     dbo.OpportunityMainDonor AS OMD ON OM.OpportunityKey = OMD.OpportunityKey LEFT OUTER JOIN
     dbo.OpportunityMainMember AS OMM ON OM.OpportunityKey = OMM.OpportunityKey LEFT OUTER JOIN
     dbo.ContactMain AS P ON OM.ProspectKey = P.ContactKey LEFT OUTER JOIN
     dbo.Institute AS I ON P.ContactKey = I.ContactKey LEFT OUTER JOIN
     dbo.GroupMain AS G ON OM.OpportunityOwnerGroupKey = G.GroupKey INNER JOIN
     dbo.GroupMember AS GM ON G.GroupKey = GM.GroupKey LEFT OUTER JOIN
     dbo.ContactMain AS CSM ON GM.MemberContactKey = CSM.ContactKey LEFT OUTER JOIN
     dbo.GroupMemberDetail AS GMD ON GM.GroupMemberKey = GMD.GroupMemberKey LEFT OUTER JOIN
     dbo.GroupRoleRef AS GRR ON GMD.GroupRoleKey = GRR.GroupRoleKey
UNION
SELECT OM.Id AS OpportunityId, OM.Description AS OpportunityName, OSR.OpportunityStatusName AS OpportunityStatus, OT.Name AS OpportunityType,
       OT.OpportunityClass, OM.CurrentActionPlanStageName AS CurrentStage, OM.WinProbability, OM.ExpirationDate, OM.UpdatedOn, OM.CreatedOn,
       CASE WHEN OMS.DecisionDate IS NOT NULL THEN OMS.DecisionDate WHEN OMD.DecisionDate IS NOT NULL
       THEN OMD.DecisionDate WHEN OMM.DecisionDate IS NOT NULL THEN OMM.DecisionDate END AS DecisionDate,
       CASE WHEN OMS.ResponseMedia IS NOT NULL THEN OMS.ResponseMedia WHEN OMD.ResponseMedia IS NOT NULL
       THEN OMD.ResponseMedia WHEN OMM.ResponseMedia IS NOT NULL THEN OMM.ResponseMedia END AS ResponseMedia,
       CASE WHEN OMS.TimingProbability IS NOT NULL THEN OMS.TimingProbability WHEN OMD.TimingProbability IS NOT NULL
       THEN OMD.TimingProbability WHEN OMM.TimingProbability IS NOT NULL THEN OMM.TimingProbability END AS TimingProbability,
       CASE WHEN OMS.ActualSales IS NOT NULL THEN OMS.ActualSales WHEN OMD.Actual IS NOT NULL
       THEN OMD.Actual WHEN OMM.Actual IS NOT NULL THEN OMM.Actual END AS Actual, CASE WHEN OMS.PotentialSales IS NOT NULL
       THEN OMS.PotentialSales WHEN OMD.Potential IS NOT NULL THEN OMD.Potential WHEN OMM.Potential IS NOT NULL
       THEN OMM.Potential END AS Potential, CASE WHEN OMS.Quality IS NOT NULL THEN OMS.Quality WHEN OMD.Quality IS NOT NULL
       THEN OMD.Quality WHEN OMM.Quality IS NOT NULL THEN OMM.Quality END AS Quality, CASE WHEN OMS. Product IS NOT NULL
       THEN OMS. Product WHEN OMD.Distribution IS NOT NULL THEN OMD.Distribution WHEN OMM.Subscription IS NOT NULL
       THEN OMM.Subscription END AS ProductInterest, CASE WHEN OMS.Units IS NOT NULL THEN OMS.Units WHEN OMM.Units IS NOT NULL
       THEN OMM.Units ELSE 0 END AS Units, P.ID AS ProspectId, P.FullName AS ProspectName, I.InstituteName AS ProspectCompany,
       G.Name AS GroupName, G.GroupTypeKey, CSM.ID AS OwnerId, CSM.FullName AS OwnerName, GRR.GroupRoleName AS OwnerRole,
       OM.OpportunityKey, OT.OpportunityTypeKey, OM.OpportunityOwnerGroupKey, OM.OpportunityContactGroupKey, OM.SourceCodeKey
FROM dbo.OpportunityMain AS OM INNER JOIN
     dbo.OpportunityType AS OT ON OM.OpportunityTypeKey = OT.OpportunityTypeKey LEFT OUTER JOIN
     dbo.OpportunityStatusRef AS OSR ON OM.OpportunityStatusCode = OSR.OpportunityStatusCode LEFT OUTER JOIN
     dbo.OpportunityMainSales AS OMS ON OM.OpportunityKey = OMS.OpportunityKey LEFT OUTER JOIN
     dbo.OpportunityMainDonor AS OMD ON OM.OpportunityKey = OMD.OpportunityKey LEFT OUTER JOIN
     dbo.OpportunityMainMember AS OMM ON OM.OpportunityKey = OMM.OpportunityKey LEFT OUTER JOIN
     dbo.ContactMain AS P ON OM.ProspectKey = P.ContactKey LEFT OUTER JOIN
     dbo.Institute AS I ON P.ContactKey = I.ContactKey LEFT OUTER JOIN
     dbo.GroupMain AS G ON OM.OpportunityContactGroupKey = G.GroupKey INNER JOIN
     dbo.GroupMember AS GM ON G.GroupKey = GM.GroupKey LEFT OUTER JOIN
     dbo.ContactMain AS CSM ON GM.MemberContactKey = CSM.ContactKey LEFT OUTER JOIN
     dbo.GroupMemberDetail AS GMD ON GM.GroupMemberKey = GMD.GroupMemberKey LEFT OUTER JOIN
     dbo.GroupRoleRef AS GRR ON GMD.GroupRoleKey = GRR.GroupRoleKey

GO